package rsshunter;

import java.sql.*;
import java.util.Formatter;
import java.util.LinkedList;
import java.util.List;

/**
 * Class for handling database using JDBC.
 */
public class DB {

    private Connection conn;
    private Statement stat;
    private PreparedStatement prep;

    public boolean addBase(RSSBase base) {
        boolean state = true;
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format(
                    "INSERT INTO bases VALUES ('%s', %d,'%s','%s',%d);",
                    base.getName(),
                    base.getType(),
                    base.getCreated(),
                    base.getNotify(),
                    base.getFrequency());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error adding new base to database: " + e);
            state = false;
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return state;
    }

    public void deleteBase(String name) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();

            //Delete entries related with base
            query.format("DELETE FROM entries "
                    + "WHERE channelid IN "
                    + "(SELECT channelid FROM channels WHERE base_name='%s');",
                    name);
            stat.executeUpdate(query.toString());

            //Delete channels related with base
            query = new Formatter();
            query.format("DELETE FROM channels WHERE base_name='%s';", name);
            stat.executeUpdate(query.toString());

            //Delete keywords related with base
            query = new Formatter();
            query.format("DELETE FROM keywords WHERE base_name='%s';", name);
            stat.executeUpdate(query.toString());

            //Finally delete base
            query = new Formatter();
            query.format("DELETE FROM bases WHERE base_name='%s';", name);
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error deleting base from database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void changeNotification(String base_name, String type) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("UPDATE bases SET notify_type='%s'"
                    + "WHERE base_name='%s'", type, base_name);
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error changing base notify type: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void changeFrequency(String base_name, int freq) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("UPDATE bases SET frequency='%d'"
                    + "WHERE base_name='%s'", freq, base_name);
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error changing base frequency: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public boolean changeBaseName(String oldName, String newName) {
        boolean state = true;
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("UPDATE bases SET base_name='%s'"
                    + "WHERE base_name='%s'", newName, oldName);
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error renaming base in db: " + e);
            state = false;
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return state;
    }

    public String addChannel(RSSChannel channel) {
        String channelId = "";
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            String title = (channel.getTitle() == null)
                    ? "NULL"
                    : channel.getTitle();
            String lastmod = (channel.getLastModified() == null)
                    ? "NULL"
                    : channel.getLastModified();
            query.format(
                    "INSERT INTO channels(uri, title, last_modified, base_name) "
                    + "VALUES ('%s','%s', '%s', '%s');",
                    channel.getURI(),
                    title,
                    lastmod,
                    channel.getBase());
            stat.executeUpdate(query.toString());
            query = new Formatter();
            query.format("SELECT channelid FROM channels "
                    + "WHERE uri='%s' AND base_name='%s'",
                    channel.getURI(), channel.getBase());
            ResultSet rs = stat.executeQuery(query.toString());
            channelId = rs.getString(1);
        } catch (SQLException e) {
            System.err.println("Error adding new channel to database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return channelId;
    }

    public void deleteChannel(RSSChannel channel) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("DELETE FROM channels WHERE "
                    + "uri='%s' AND base_name='%s'",
                    channel.getURI(), channel.getBase());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error deleting chnnel from database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void updateChannel(RSSChannel old, String newUri) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("UPDATE channels "
                    + "SET uri='%s' "
                    + "WHERE uri='%s' AND base_name='%s'",
                    newUri, old.getURI(), old.getBase());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error updating channel uri in database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void updateChannel(RSSChannel channel) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("UPDATE channels "
                    + "SET uri='%s', title='%s', last_modified='%s' "
                    + "WHERE channelid='%s'",
                    channel.getURI(),
                    channel.getTitle(),
                    channel.getLastModified(),
                    channel.getId());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error updating channel in database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void addEntry(RSSEntry item) {
        try {
            String date = (item.getDate() == null) ? "NULL" : item.getDate();
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format(
                    "INSERT INTO entries("
                    + "title,uri,description,created,"
                    + "reported,channelid) "
                    + "VALUES ('%s','%s','%s','%s',%d,'%s');",
                    item.getTitle(),
                    item.getUri(),
                    item.getDescription(),
                    date,
                    0,
                    item.getChannel());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error adding new entry to database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void addEntries(List<RSSEntry> entries) {
        try {
            connect();
            prep = conn.prepareStatement(
                    "INSERT INTO entries("
                    + "title,uri,description,created,"
                    + "reported,channelid) "
                    + "VALUES (?, ?, ?, ?, ?, ?);");
            for (RSSEntry entry : entries) {
                String date = (entry.getDate() == null) ? "NULL" : entry.getDate();
                prep.setString(1, entry.getTitle());
                prep.setString(2, entry.getUri());
                prep.setString(3, entry.getDescription());
                prep.setString(4, date);
                prep.setInt(5, 0);
                prep.setString(6, entry.getChannel());
                prep.addBatch();

            }
            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("Error adding multiple entries "
                    + "to database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void deleteEntry(RSSEntry entry) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("DELETE FROM entries WHERE entryid=%s",
                    entry.getId());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error deleting entry from database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void deleteEntries(List<RSSEntry> entries) {
        try {
            connect();
            prep = conn.prepareStatement(
                    "DELETE FROM entries WHERE entryid=?;");
            for (RSSEntry entry : entries) {
                prep.setString(1, entry.getId());
                prep.addBatch();
            }
            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("Error deleting  multiple entries "
                    + "from database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void addKeyword(RSSKeyword keyword) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format(
                    "INSERT INTO keywords(word, base_name) "
                    + "VALUES ('%s','%s');",
                    keyword.getValue(),
                    keyword.getBase());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error adding keyword to database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void updateKeyword(RSSKeyword oldKeyword, String newKeyword) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("UPDATE keywords "
                    + "SET word='%s' "
                    + "WHERE word='%s' AND base_name='%s'",
                    newKeyword, oldKeyword.getValue(), oldKeyword.getBase());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error updating keyword in database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void deleteKeyword(RSSKeyword keyword) {
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("DELETE FROM keywords WHERE "
                    + "word='%s' AND base_name='%s'",
                    keyword.getValue(), keyword.getBase());
            stat.executeUpdate(query.toString());
        } catch (SQLException e) {
            System.err.println("Error deleting keyword from database: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public LinkedList<RSSBase> getBases() {
        LinkedList<RSSBase> result = new LinkedList<RSSBase>();
        try {
            RSSBase actual;
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT base_name, type, created,"
                    + "notify_type,frequency "
                    + "FROM bases;");
            while (rs.next()) {
                actual = new RSSBase(rs.getString(1));
                actual.setType(rs.getByte(2));
                actual.setCreated(rs.getString(3));
                actual.setNotify(rs.getString(4));
                actual.setFrequency(rs.getInt(5));
                result.add(actual);
            }
        } catch (SQLException e) {
            System.err.println("Error getting bases: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public List<RSSKeyword> getKeywords(String base) {
        LinkedList<RSSKeyword> result = new LinkedList<RSSKeyword>();
        try {
            RSSKeyword actual;
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT word FROM Keywords WHERE "
                    + "base_name='" + base + "'");
            while (rs.next()) {
                actual = new RSSKeyword(rs.getString(1), base);
                result.add(actual);
            }
        } catch (SQLException e) {
            System.err.println("Error getting keywords: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public List<RSSChannel> getChannels(String base) {
        LinkedList<RSSChannel> result = new LinkedList<RSSChannel>();
        try {
            RSSChannel actual;
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT channelid,uri,title,last_modified "
                    + "FROM channels WHERE "
                    + "base_name='" + base + "'");
            while (rs.next()) {
                actual = new RSSChannel(rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        base);
                result.add(actual);
            }
        } catch (SQLException e) {
            System.err.println("Error getting channels: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public LinkedList<RSSEntry> getEntries(RSSBase base) {
        LinkedList<RSSEntry> result = new LinkedList<RSSEntry>();
        try {
            RSSEntry actual;
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT e.entryid,e.title,e.uri,e.description,e.created, "
                    + "e.reported,ch.uri FROM entries e "
                    + "JOIN channels ch USING (channelid) "
                    + "WHERE ch.base_name='" + base.getName() + "'");
            while (rs.next()) {
                actual = new RSSEntry(rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getBoolean(6),
                        rs.getString(7));

                result.add(actual);
            }
        } catch (SQLException e) {
            System.err.println("Error getting entries: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public RSSBase getBase(String name) {
        RSSBase result = null;
        try {
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT base_name, type, created,"
                    + "notify_type,frequency "
                    + "FROM bases WHERE base_name='" + name + "'");
            if (rs.next()) {
                result = new RSSBase(rs.getString(1),
                        rs.getByte(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5));
            }
        } catch (SQLException e) {
            System.err.println("Error getting base: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public RSSChannel getChannel(String uri, String baseName) {
        RSSChannel result = null;
        try {
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("SELECT channelid, uri, title, "
                    + "last_modified, base_name FROM channels "
                    + "WHERE uri='%s' AND base_name='%s'",
                    uri, baseName);
            ResultSet rs = stat.executeQuery(query.toString());
            if (rs.next()) {
                result = new RSSChannel(rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5));
            }
        } catch (SQLException e) {
            System.err.println("Error getting channel: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public List<RSSChannel> getChannels(String[] uris, String baseName) {
        LinkedList<RSSChannel> result = new LinkedList<RSSChannel>();
        StringBuilder uriList = new StringBuilder();
        for (String uri : uris) {
            uriList.append("'").append(uri).append("'").append(", ");
        }
        uriList.delete(uriList.length() - 2, uriList.length());

        try {
            RSSChannel actual;
            connect();
            stat = conn.createStatement();
            Formatter query = new Formatter();
            query.format("SELECT channelid, uri, title, "
                    + "last_modified, base_name FROM channels "
                    + "WHERE uri in (%s) AND base_name='%s'",
                    uriList.toString(), baseName);
            ResultSet rs = stat.executeQuery(query.toString());

            while (rs.next()) {
                actual = new RSSChannel(rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5));
                result.add(actual);
            }
        } catch (SQLException e) {
            System.err.println("Error getting channels: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    /**
     * Get list of objects which represents entries in main GUI window.
     *
     * @param base Base of entries.
     * @return List of rows to jTable.
     */
    public LinkedList<Object[]> getEntriesList(RSSBase base) {
        LinkedList<Object[]> result = new LinkedList<Object[]>();
        Object[] actual;
        try {
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT e.title,ch.uri FROM entries e "
                    + "JOIN channels ch USING (channelid) "
                    + "WHERE ch.base_name='" + base.getName() + "'");
            while (rs.next()) {
                actual = new Object[]{rs.getString(1), rs.getString(2)};
                result.add(actual);
            }
        } catch (SQLException e) {
            System.err.println("Error getting entries list: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public UserData getUserData() {
        UserData result = new UserData("", "", "", "");
        try {
            connect();
            stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(
                    "SELECT notifyMail, notifyPhone, gmailLogin, gmailPassword FROM user");
            if (rs.next()) {
                result = new UserData(rs.getString(1), rs.getString(2),
                        rs.getString(3), rs.getString(4));
            }
        } catch (SQLException e) {
            System.err.println("Error getting user data: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
        return result;
    }

    public void setUserData(UserData ud) {
        try {
            connect();
            stat = conn.createStatement();
            ResultSet rs =
                    stat.executeQuery(
                    "SELECT notifyMail, notifyPhone, gmailLogin, gmailPassword "
                    + "FROM user");
            if (rs.next()) {
                // User data exists
                Formatter query = new Formatter().format(
                        "UPDATE user SET notifyMail='%s', notifyPhone='%s', "
                        + "gmailLogin='%s', gmailPassword='%s'",
                        ud.getMail(), ud.getPhone(),
                        ud.getGmailLogin(), ud.getGmailPassword());
                stat.executeUpdate(query.toString());
            } else {
                // There is no user data in database
                Formatter query = new Formatter().format(
                        "INSERT INTO user VALUES ('%s', '%s', '%s', '%s')",
                        ud.getMail(), ud.getPhone(), ud.getGmailLogin(),
                        ud.getGmailPassword());
                stat.executeUpdate(query.toString());
            }
        } catch (SQLException e) {
            System.err.println("Error setting user data: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    public void create() {
        try {
            connect();
            stat = conn.createStatement();
            conn.setAutoCommit(false);
            stat.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS bases (   "
                    + "    base_name VARCHAR(30) PRIMARY KEY,              "
                    + "    type INTEGER NOT NULL,                  "
                    + "    created DATE NOT NULL,              "
                    + "    notify_type VARCHAR(6) NOT NULL,   "
                    + "    frequency INTEGER NOT NULL                  "
                    + ");                                              ");
            stat.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS channels (          "
                    + "    channelid INTEGER PRIMARY KEY,                      "
                    + "    uri VARCHAR(200) NOT NULL,                  "
                    + "    title VARCHAR(100),                         "
                    + "    last_modified DATE,                         "
                    + "    base_name VARCHAR(30) NOT NULL       "
                    + "            REFERENCES bases         "
                    + ");                                              ");
            stat.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS keywords (     "
                    + "    keywordid INTEGER PRIMARY KEY,                      "
                    + "    word VARCHAR(100) NOT NULL,                "
                    + "    base_name VARCHAR(30) NOT NULL       "
                    + "            REFERENCES bases         "
                    + ");                                              ");
            stat.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS entries (          "
                    + "        entryid INTEGER PRIMARY KEY,                  "
                    + "        title VARCHAR(40) NOT NULL,             "
                    + "        uri VARCHAR(200) NOT NULL,              "
                    + "        description TEXT NOT NULL,                    "
                    + "        created DATE,                   "
                    + "        reported INTEGER NOT NULL,           "
                    + "        channelid INTEGER NOT NULL                  "
                    + "                REFERENCES channel            "
                    + ");                                              ");
            stat.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS user (               "
                    + "        notifyMail VARCHAR(50),                 "
                    + "        notifyPhone VARCHAR(20),                "
                    + "        gmailLogin VARCHAR(30),                 "
                    + "        gmailPassword VARCHAR(50)                   "
                    + ");                                              ");
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("Database creating error: " + e);
        } finally {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }

    private void connect() {
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:data.db");
        } catch (SQLException e) {
            System.err.println("Database connection error: " + e);
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC library not found: " + e);
        }
    }
}
